import os
import xlwt
import arrow
import time
import pandas as pd
# from openpyxl import load_workbook


class ExcelObj:  # excel操作类
    def __init__(self, TableData, filePath):
        self.filePath = filePath
        self.workbook = xlwt.Workbook()
        self.worksheet = self.workbook.add_sheet('定位数据')
        for index in range(len(TableData)):
            self.worksheet.write(0, index, TableData[index])

    def write_data_to_xls(self, row, column, string):
        self.worksheet.write(row, column, string)
        # date = time.strftime("%Y-%m-%d-%H-%M-%S", time.localtime())

    def save(self):
        self.workbook.save(self.filePath)


class PandasExcelObj:  # pandas库excel操作类
    def __init__(self, filePath, TableName, SheetName):
        self.filePath = filePath  # 保存的文件名
        self.TableHeader = TableName  # 表头
        self.SheetName = SheetName  # sheet名字
        self.writer = pd.ExcelWriter(self.filePath, engine='openpyxl')  # 可以向不同的sheet写入数据
        self.df = pd.DataFrame()
        for sheet in self.SheetName:  # 初始化多个sheet
            self.df.to_excel(self.writer, sheet_name=sheet)

    def write_data_to_xls(self, WriteData, SheetName):  # WriteData列表形式，指定sheetName
        self.df = pd.DataFrame(WriteData, columns=self.TableHeader)  # 列表数据转为数据框
        self.df.to_excel(self.writer, sheet_name=SheetName)  # 将数据写入excel中的SheetName

    def save(self):
        self.writer.save()  # 保存


def ReadFlashHistory(filename, excelObj):  # xlwt库写入excel数据，最大65535行
    row = 1
    with open(filename, encoding='gbk') as fp:
        data = fp.readlines()
        for line_data in data:
            if (len(line_data) < 80):
                print('文件第%d' % data.index(line_data), '行格式不正确')

            line_data = line_data.strip('；\r\n')
            line_data = line_data.split(',')

            SerialNum = line_data[0]  # 序号
            DateStrTemp = line_data[1][2:]  # 时间
            DateStr = DateStrTemp[0:4] + '-' + DateStrTemp[4:6] + '-' + DateStrTemp[6:8] + ' '
            DateStr += DateStrTemp[9:11] + ':' + DateStrTemp[11:13] + ':' + DateStrTemp[13:15]

            PosTotal = float(line_data[2][4:])  # 正向累计
            RevTotal = float(line_data[3][4:])  # 反向累计

            InsFlowTemp = line_data[4][4:]  # 瞬时流量
            if InsFlowTemp[0:1] == '+':
                InsFlow = float(InsFlowTemp[1:])
            elif (InsFlowTemp[0:1] == '-'):
                InsFlow = 0 - float(InsFlowTemp[1:])
                print(InsFlowTemp[0:1])

            InsSpeedTemp = line_data[5][4:]  # 瞬时流速
            if InsSpeedTemp[0:1] == '+':
                InsSpeed = float(InsSpeedTemp[1:])
            elif (InsSpeedTemp[0:1] == '-'):
                InsSpeed = 0 - float(InsSpeedTemp[1:])
                print(InsSpeedTemp[0:1])

            excelObj.write_data_to_xls(row, 0, SerialNum)  # 序号
            excelObj.write_data_to_xls(row, 1, DateStr)  # 时间
            excelObj.write_data_to_xls(row, 2, PosTotal)  # 正向累计
            excelObj.write_data_to_xls(row, 3, RevTotal)  # 反向累计
            excelObj.write_data_to_xls(row, 4, InsFlow)  # 瞬时流量
            excelObj.write_data_to_xls(row, 5, InsSpeed)  # 瞬时流速
            row += 1
    excelObj.save()


def PandasFunc(filename, PandasExcelObj):  # pandas方法写入Excel数据
    WriteData = []  # 需要写入Excel的数据
    with open(filename, encoding='gbk') as fp:
        data = fp.readlines()
        for line_data in data:
            if (len(line_data) < 80):
                print('文件第%d' % data.index(line_data), '行格式不正确')

            line_data = line_data.strip('；\r\n')
            line_data = line_data.split(',')

            SerialNum = line_data[0]  # 序号
            DateStrTemp = line_data[1][2:]  # 时间
            DateStr = DateStrTemp[0:4] + '-' + DateStrTemp[4:6] + '-' + DateStrTemp[6:8] + ' '
            DateStr += DateStrTemp[9:11] + ':' + DateStrTemp[11:13] + ':' + DateStrTemp[13:15]

            PosTotal = float(line_data[2][4:])  # 正向累计
            RevTotal = float(line_data[3][4:])  # 反向累计

            InsFlowTemp = line_data[4][4:]  # 瞬时流量
            if InsFlowTemp[0:1] == '+':
                InsFlow = float(InsFlowTemp[1:])
            elif (InsFlowTemp[0:1] == '-'):
                InsFlow = 0 - float(InsFlowTemp[1:])
                # print(InsFlowTemp[0:1])

            InsSpeedTemp = line_data[5][4:]  # 瞬时流速
            if InsSpeedTemp[0:1] == '+':
                InsSpeed = float(InsSpeedTemp[1:])
            elif (InsSpeedTemp[0:1] == '-'):
                InsSpeed = 0 - float(InsSpeedTemp[1:])
                # print(InsSpeedTemp[0:1])

            WriteData.append((SerialNum, DateStr, PosTotal, RevTotal, InsFlow, InsSpeed))

    PandasExcelObj.write_data_to_xls(WriteData, 'Flash历史数据')  # 数据,sheetName
    PandasExcelObj.save()  # 保存


if __name__ == "__main__":
    txtFileName = "./ReceivedTofile-COM7-2022_3_17_15-29-40.DAT"

    saveExcelfileName = 'Flash历史记录数据解析-{}.xlsx'.format(arrow.now().date())
    print('-->保存的Excel文件名为：', saveExcelfileName)
    ExcelfilePath = os.path.join(os.getcwd(), saveExcelfileName)

    # tableHeader = ['序号', '时间', '正想累计', '反向累计', '瞬时流量', '瞬时流速']
    # excelObj = ExcelObj(tableHeader, ExcelfilePath)
    # ReadFlashHistory(txtFileName, excelObj)

    # pandas方法
    TableName = ['序号', '时间', '正向累计', '反向累计', '瞬时流量', '瞬时流速']
    SheetName = ['Flash历史数据', 'flash2']
    PandasExcelObj = PandasExcelObj(ExcelfilePath, TableName, SheetName)
    PandasFunc(txtFileName, PandasExcelObj)
